#!/usr/bin/env Python
# coding=utf-8
import pymysql
import importlib
from Applications.config.secure import PooledDBSecure
# from DBUtils.PooledDB import PooledDB
from dbutils.pooled_db import PooledDB
from Applications.libs.logHelper import logger
from timeit import default_timer


class create_pool(object):
    __pool = None

    def __init__(self):
        if self.__pool is None:
            self.__class__.__pool = PooledDB(
                creator=pymysql,  # 使用链接数据库的模块
                # 使用链接数据库的模块import pymysql
                # 连接池允许的最大连接数，0和None表示不限制连接数
                maxconnections=6,
                # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
                mincached=2,
                # 链接池中最多闲置的链接，0和None不限制
                maxcached=5,
                # 链接池中最多共享的链接数量，0和None表示全部共享。
                # 因为pymysql和MySQLdb等模块的 threadsafety都为1，
                # 所有值无论设置为多少，maxcached永远为0，所以永远是所有链接都共享。
                maxshared=3,
                # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
                blocking=True,
                # 一个链接最多被重复使用的次数，None表示无限制
                maxusage=None,
                # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
                setsession=[],
                # ping MySQL服务端，检查是否服务可用。
                #  如：0 = None = never, 1 = default = whenever it is requested,
                # 2 = when a cursor is created, 4 = when a query is executed, 7 = always
                ping=0,
                #**DevelopmentConfig.DATABASE_CONFIG
                #**base_config
                #**current_app.config.get("DATABASE_CONFIG")
                ** PooledDBSecure.DATABASE_CONFIG
            )

    def get_conn(self):
        logger.info('池子里目前有 %s  ' % len(self.__pool._idle_cache))
        return self.__pool.connection()

#def get_connection():
#    return create_pool()


'''
def database_init(app):
    global get_connection
    get_connection = create_pool(app.config.get("DATABASE_CONFIG"))
'''
g_pool_connection = create_pool()
class queryHelper(object):

    def __init__(self, commit=True, log_time=True, log_label='总用时', pool=None):
        """
        :param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
        :param log_time:  是否打印程序运行总时间
        :param log_label:  自定义log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label
        self._start = 0
        #global get_connection
        self._pool = g_pool_connection

    def __enter__(self):
        # 如果需要记录时间
        if self._log_time is True:
            self._start = default_timer()
        self._conn = self._pool.get_conn()
        #self._conn = self._pool.get_conn()
        #self._conn = g_pool_connection.get_conn()
        #self._conn = self._pool.get_conn()
        # conn = create_pool().get_conn()
        self._cursor = self._conn.cursor(pymysql.cursors.DictCursor)
        self._conn.autocommit = False

        return self

    def __exit__(self, *exc_info):
        # 提交事务
        if self._commit:
            self._conn.commit()
        # 在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            logger.info('-- %s: %.6f 秒' % (self._log_label, diff))


    def fetch(self, sql, params=None, size=None):
        self.cursor.execute(sql, params)
        logger.info("SQL->%s"%(sql))
        if size:
            result = self.cursor.fetchall()
        else:
            result = self.cursor.fetchone()
        return result

    def execute(self,sql,args=None):
        with self._conn.cursor(pymysql.cursors.DictCursor) as cursor:
            result = cursor.execute(sql,args)
            self.close(self._conn, cursor)
            return result


    @property
    def cursor(self):
        return self._cursor


''''
    @classmethod
    def select(self,sql,args=None,size=None):
        self._start = default_timer()
        #from flask import current_app
        #g_pool_connection = create_pool()
        conn = g_pool_connection.getConn()
        #conn = current_app.db.getConn()
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, args)
            if size:
                result = cursor.fetchall()
            else :
                result = cursor.fetchone()
            cursor.close()
            conn.close()

            diff = default_timer() - self._start
            logger.info('用时:----%s----' % diff)

            return result

    def execute(self,sql,args=None):
        conn = self.pool.connection()
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            result = cursor.execute(sql,args)
            self.close(conn, cursor)
            return result

    @classmethod
    def callproc_sp_page(self,*args):
        conn = self.pool.connection()
        totalcount =pagecount = 0
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.callproc('sp_Page',args)
            result = cursor.fetchall()
            if result:
                cursor.execute('SELECT @_sp_Page_6,@_sp_Page_7')
                result_count = cursor.fetchone()
                if result_count:
                    totalcount = result_count['@_sp_Page_6']
                    pagecount = result_count['@_sp_Page_7']

            cursor.close()
            conn.close()
            return result,totalcount,pagecount





def current_database(app):
    database_type = app.config.get('DATABASE_TYPE')
    database_config = app.config.get('DATABASE_CONFIG')
    if database_type == 'mysql':
        from .PooledBQuery import queryHelper
        db = queryHelper(database_config)
        logger.info(db)
        app.dbaseconn = db




class POOLConnection(object):
    def __init__(self,**kwargs):
        logger.info('create database connection create_pool ')
        self.__pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=None,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=10,  # 链接池中最多闲置的链接，0和None不限制
            maxshared=1,  # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。
            # 如：0 = None = never,
            # 1 = default = whenever it is requested,
            # 2 = when a cursor is created,
            # 4 = when a query is executed,
            # 7 = always
            host=kwargs.get('host'),
            port=kwargs.get('port'),
            user=kwargs.get('user'),
            password=kwargs.get('passwd'),
            database=kwargs.get('db'),
            charset=kwargs.get('charset')
        )


    def select(self,sql,args=None,size=None):
        conn = self.__pool.connection()
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql, args)
            if size:
                result = cursor.fetchall()
            else :
                result = cursor.fetchone()
            cursor.close()
            conn.close()
            return result

    def execute(self,sql,args=None):
        conn = self.__pool.connection()
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            result = cursor.execute(sql,args)
            conn.commit()
            cursor.close()
            conn.close()
        return result

    def callproc_sp_page(self,*args):
        conn = self.__pool.connection()
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            totalcount =pagecount = 0

            callproc=cursor.callproc('sp_Page',args)
            result = cursor.fetchall()
            if result:
                execute = cursor.execute('SELECT @_sp_Page_6,@_sp_Page_7')
                result_count = cursor.fetchone()
                if result_count:
                    totalcount = result_count['@_sp_Page_6']
                    pagecount = result_count['@_sp_Page_7']

            cursor.close()
            conn.close()
            return result,totalcount,pagecount

'''


def log(self, sql, args=()):
    logger.info('SQL: %s' % sql)
